# -*- coding: utf-8 -*-
import pymysql
import datetime
import json


def get_conn(dorsi_jdbc_json):
    conn = pymysql.connect(host=dorsi_jdbc_json.get('host'), port=dorsi_jdbc_json.get('port'),
                           user=dorsi_jdbc_json.get('user'), passwd=dorsi_jdbc_json.get('password'), db='jms_dwd',
                           charset='utf8')
    return conn


def execute(sql, dorsi_jdbc_json):
    conn = get_conn(dorsi_jdbc_json)
    cursor = conn.cursor()
    cursor.execute(sql)
    conn.close()


def main(begin_date, dorsi_jdbc_pas):
    dorsi_jdbc_json = json.loads(dorsi_jdbc_pas.replace("'", '"'))
    begin_date = datetime.datetime.strptime(begin_date, "%Y-%m-%d")
    today = (begin_date + datetime.timedelta(days=-3)).strftime("%Y-%m-%d")
    partition = (begin_date + datetime.timedelta(days=-3)).strftime("%Y%m%d")
    droppartition = """TRUNCATE TABLE jms_dws.dws_agency_area PARTITION (p{partition});""".format(partition=partition)
    dws_agency_area = """INSERT INTO jms_dws.dws_agency_area 
	SELECT
	taking_date,
	type,
	start_subordinate_agent_code,
	start_subordinate_agent_name,
	count( 1 ) AS total,
	sum( CASE WHEN prescription = 0 THEN 1 ELSE 0 END ) AS zeroday_num,
	round( sum( CASE WHEN prescription = 0 THEN 1 ELSE 0 END ) / count( 1 ) * 100, 2 ) AS zeroday_rate,
	sum( CASE WHEN prescription = 1 THEN 1 ELSE 0 END ) AS oneday_num,
	round( sum( CASE WHEN prescription = 1 THEN 1 ELSE 0 END ) / count( 1 ) * 100, 2 ) AS oneday_rate,
	sum( CASE WHEN prescription = 2 THEN 1 ELSE 0 END ) AS twoday_num,
	round( sum( CASE WHEN prescription = 2 THEN 1 ELSE 0 END ) / count( 1 ) * 100, 2 ) AS twoday_rate,
	sum( CASE WHEN prescription = 3 THEN 1 ELSE 0 END ) AS threeday_num,
	round( sum( CASE WHEN prescription = 3 THEN 1 ELSE 0 END ) / count( 1 ) * 100, 2 ) AS threeday_rate 
FROM
	(
	SELECT
		taking_date,
		start_subordinate_agent_code,
		start_subordinate_agent_name,
		prescription,
	CASE
			
			WHEN start_city_id = sign_city_id THEN
			1 
			WHEN start_provider_id = sign_provider_id THEN
			2 
			WHEN start_provider_id <> sign_provider_id THEN
			3 
		END AS type --类型（ 1-同城 2-同省 3-跨省） 
	FROM
		jms_dwd.dwd_waybill_prescription_reach_details 
	WHERE
		taking_date = '{today}' 
		AND districtprescription IS NOT NULL 
	) t 
GROUP BY
	taking_date,
	start_subordinate_agent_code,
	start_subordinate_agent_name,
	type;""".format(today=today)
    print('sql====================================' + dws_agency_area)
    execute(droppartition, dorsi_jdbc_json)
    execute(dws_agency_area, dorsi_jdbc_json)
